/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bsc.war.form;

import bsc.ejb.entity.Comparator;
import bsc.ejb.entity.ComparatorDimensionalCut;
import bsc.ejb.entity.DimensionalCut;
import bsc.ejb.entity.Pointer;
import bsc.war.delegate.DelegateBSC;
import bsc.war.factory.Factory;
import bsc.war.service.BSCUtils;
import bsc.war.service.FileUtils;
import bsc.war.service.ScoreCardUtils;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.AbstractController;

//org.apache.commons.validator.GenericValidator
/**
 *
 * @author luisZavaleta
 */
public class UpdateData extends AbstractController {

    @Override
    protected ModelAndView handleRequestInternal(HttpServletRequest request,
            HttpServletResponse response) throws Exception {
        boolean result = true;
        try {

            DelegateBSC delegate = (DelegateBSC) Factory.getBean("DelegateBSC");
            processRequestInternal(request, response, delegate);
            
            result = true;

        } catch (Exception e) {
            e.printStackTrace();
            result = false;
        }
        request.setAttribute("result", result);
        return new ModelAndView("result");
    }

    public static void processRequestInternal(HttpServletRequest request,
            HttpServletResponse response, DelegateBSC delegate) throws Exception {

            //scorecardId="+scorecardId+"&title="+title+"&note="+note;
            Map<String, Object> parameterValues = FileUtils.getMultipartParameters(request);
            Object file = parameterValues.get("file");

            byte[] attachmentFile = file == null ? null : (byte[]) file;
            String attachmentFileName = (String) parameterValues.get("file_filename");

            //InputStream fis = Main.class.getResourceAsStream(nombreKPI);
            InputStream fis = new ByteArrayInputStream(attachmentFile);

            POIFSFileSystem fs = new POIFSFileSystem(fis);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            int sheets = wb.getNumberOfSheets();

            for (int s = 0; s < sheets; s++) {

                //Hoja de trabajo
                //String ht= nombreKPI.substring(0,nombreKPI.length()-4 );
                HSSFSheet sheet = wb.getSheetAt(s);
                String ht = sheet.getSheetName();
                //System.out.println("Hoja: " + ht);

                //Get id Pointer
                HSSFRow rowPointer = sheet.getRow(0);
                Long idPointer = (long) rowPointer.getCell(1).getNumericCellValue();
                Pointer pointer = (Pointer) delegate.findScoreCard(idPointer);
                //System.out.println("Pointer: " + pointer.getName());


                //Get Comaparator
                HSSFRow rowComparator = sheet.getRow(1);
                Long idComparator = (long) rowComparator.getCell(1).getNumericCellValue();
                //System.out.println("El id del comparador es: " + idComparator);
                Comparator comparator = delegate.findComparator(idComparator);
                //System.out.println("Comparador: " + comparator.getDescription());

                //Obtengo la lista de cortes
                List<DimensionalCut> dimensionalCuts = new ArrayList<DimensionalCut>();

                HSSFRow rowCuts = sheet.getRow(3);
                int rowCutsNumber = rowCuts.getLastCellNum();

                for (int i = 1; i < rowCutsNumber; i++) {
                    String cutsIdDescription[] = rowCuts.getCell(i).getStringCellValue().split("-");
                    Long idDimensionalCut = Long.parseLong(cutsIdDescription[0]);
                    DimensionalCut dimensionalCut = delegate.findDimensionalCut(idDimensionalCut);
                    dimensionalCuts.add(dimensionalCut);
                }

                //Obtengo la lista de dates

                List<Date> dates = new ArrayList<Date>();
                Map<Date, HSSFRow> dateRow = new HashMap<Date, HSSFRow>();

                int inicioFechas = 4;
                int finFechas = sheet.getLastRowNum();

                //System.out.println("Fin Fechas: " + finFechas);

                for (int i = inicioFechas; i < finFechas + 1; i++) {
                    HSSFRow rowDate = sheet.getRow(i);

                    Date date = rowDate.getCell(0).getDateCellValue();

                    if (ScoreCardUtils.checkDate(date, pointer)) {
                        dates.add(date);
                        dateRow.put(date, rowDate);
                    }

                }
                //System.out.println("tam dates: " + dates.size());
                //Se agregan los datos
                for (int i = 0; i < dates.size(); i++) {
                    Date date = dates.get(i);
                    for (int j = 0; j < dimensionalCuts.size(); j++) {
                        DimensionalCut dimensionalCut = dimensionalCuts.get(j);
                        HSSFRow rowData = dateRow.get(date);
                        HSSFCell rowCell = rowData.getCell(j + 1, HSSFRow.CREATE_NULL_AS_BLANK);
                        //System.out.println("Valor de la celda: " + rowCell.getStringCellValue());
                        //System.out.println("Obtuve el cell");
                        //System.out.println("Fecha: " + date.toString());
                        //System.out.println("Corte: " + dimensionalCut.getIdDimensionalCut());

                        //if (!rowCell.getStringCellValue().equals("")) {
                        if (rowCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            //System.out.println("Obtendré el dato");
                            double data = rowData.getCell(j + 1).getNumericCellValue();
                            data = BSCUtils.roundNumber(data, 2);
                           
                            ComparatorDimensionalCut comparatorDimensionalCut = delegate.findComparatorDimensionalCut(comparator, dimensionalCuts.get(j), date);
                            if (comparatorDimensionalCut == null) {
                                ComparatorDimensionalCut cdc = (ComparatorDimensionalCut) Factory.getBean("ComparatorDimensionalCut");
                                cdc.setComparator(comparator);
                                cdc.setDimensionalCut(dimensionalCut);
                                cdc.setRegisterDate(date);
                                cdc.setRegister(data);

                                delegate.createComparatorDimensionalCut(cdc);
                                //System.out.println("Subi data: " + data);
                            } else {
                                comparatorDimensionalCut.setRegister(data);

                                delegate.updateComparatorDimensionalCut(comparatorDimensionalCut);
                                //System.out.println("Subi data: " + data);

                            }
                        } else if (rowCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            String data = rowData.getCell(j + 1).getStringCellValue();
                            data=data.toUpperCase();
                            if (data.equals("NA")) {
                                ComparatorDimensionalCut comparatorDimensionalCut = delegate.findComparatorDimensionalCut(comparator, dimensionalCuts.get(j), date);
                                if (comparatorDimensionalCut != null) {
                                    delegate.deleteComparatorDimensionalCut(comparatorDimensionalCut);
                                }
                            }
                        }
                    }
                }

                //CÁLCULO!!!

                boolean result = BSCUtils.calculateScoreCard(delegate, pointer, dates);
            }
            
            BSCUtils.normalUpdateDate(fs, delegate);
    }
}
